1. Field of the Invention
The present invention relates generally to a universal calling interface for invoking stored procedures on a server, and in particular, to a method for executing stored procedures that eliminates the requirement for identifying the parameters or their types prior to invoking the stored procedure.
2. Description of the Related Art
Stored procedures are collections of pre-defined procedural code that typically employ SQL to access databases. There are a number of benefits in using stored procedures, including function encapsulation, performance enhancement, client-server processing, and security. Stored procedures are not unlike procedures found in other high level languages, in that input and output parameters can be passed to stored procedures. Generally, stored procedures may take any number of parameters, which are defined internally within the procedure.
For example, following is an example of an SQL stored procedure:
CREATE PROCEDURE PROC PA1 SELECT ROW FROM TABLE WHERE
@PARAM1 INTEGER PA2 @PARAM2 LONG PA2 @PARAM3 STRING PA2 (A=PARAM1 AND PA2 B=PARAM2 AND PA2 C=PARAM3)
In the above example, the stored procedure is named "PROC" and is passed three parameters, "PARAM1", "PARAM2", and "PARAM3" are of type integer, long, and string, respectively.
Generally, a user explicitly invokes stored procedures in an interactive environment, but such procedures can also be invoked by other programs. For example, a stored procedure may be called from a VisualBasic application or interpreter, or an Open DataBase Connectivity (ODBC) application, or any number of other environments. In these prior art methods, the parameters for the stored procedure are defined or described prior to the call
For example, the following is an example of a VisualBasic program using ODBC/CLI to invoke a stored procedure:
Sub main ( ) Dim henv As Long ' Environment handle Dim hdbc As Long ' Database connection handle Dim hstmt As Long ' Statement handle Dim rc As Integer ' Return codes Dim stmt As String Dim plen(1) As Long Dim Tab_Name As String stmt = "CALL inpsrv(?)" Tab_Name = "PRESIDENT" rc = SQLAllocEnv(henv) rc = SQLAllocConnect(henv, hdbc) rc = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) rc = SQLConnect(hdbc, "sample", Len("sample"), "userid", Len("userid"), "password", Len("password")) rc = SQLAllocStmt(hdbc, hstmt) rc = SQLPrepare(hstmt, stmt, Len(stmt)) plen(0) = Len(Tab_Name) rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_BASIC_STRING, SQL_CHAR, Len(Tab_Name), 0, ByVal Tab_Name, Len(Tab_Name)) rc = SQLExecute(hstmt) rc = SQLFreeStmt(hstmt, SQL_DROP) rc = SQLTransact(henv, hdbc, SQL_COMMIT) rc = SQLDisconnect(hdbc) rc = SQLFreeConnect(hdbc) rc = SQLFreeEnv(henv) End Sub
In the above example, the "SQLAllocStmt", "SQLPrepare", "SQLBindParameter", "SQLExecute", and "SQLFreeStmt" statements are necessary to pass a parameter to a stored procedure. As can be seen, a lot of programming is required to describe the parameters passed to a stored procedure. This programming is inefficient and redundant. Thus, there is a need in the art for a more efficient method of invoking stored procedures with automated parameter checking.